package kr.or.kead.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.Handicap;
import kr.or.kead.utils.MySqlCon;

public class DaoHandicap implements DaoTable {

	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Handicap handi = (Handicap)obj;
		
		String sql = "insert into handicap values(?,?)";
		
		PreparedStatement pstmt = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, handi.getCode());
			pstmt.setString(2, handi.getName());
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally{
			try {pstmt.close(); con.close();} catch (Exception e) {	e.printStackTrace();}
		}
		return 0;
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
		Handicap handi = (Handicap)obj;
		
		String sql = "update handicap set name=? where code = ?";
		
		PreparedStatement pstmt = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, handi.getName());
			pstmt.setInt(2, handi.getCode());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally{
			try {pstmt.close(); con.close();} catch (Exception e) {	e.printStackTrace();}
		}
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
				
		String sql = "delete from handicap where code =?";
		
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		
		String sql = "select name from handicap";
		
		ArrayList<Object> handicaps = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				handicaps.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return handicaps;
	}

	@Override
	public Object selectTable(int stdId) {
		Connection con = MySqlCon.getConn();
				
		String sql = "select name from handicap where code =?";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, stdId);
			rs = pstmt.executeQuery();
			if(rs.next()){
				return rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {pstmt.close(); con.close(); rs.close();} catch (Exception e) {	e.printStackTrace();}
		}
		return null;
	}
	public int selectCodeHandiByName(String name){
		Connection con = MySqlCon.getConn();
		
		String sql = "select code from handicap where name =?";
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int res = -1;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			if(rs.next()){
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally{
			try {pstmt.close(); con.close(); rs.close();} catch (Exception e) {	e.printStackTrace();}
		}
		return res;
	}

	@Override
	public ArrayList<String> selectTableAllList() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Object selectTableByEmail(String email) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int updatePasswd(String pass, String email) {
		// TODO Auto-generated method stub
		return 0;
	}
}
